/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package CONTROLLERS;

import CONTROLLERS.ProductController;
import BEANS.ComboItem;
import BEANS.Product;
import UTIL.DatabaseConnector;
import UTIL.InputValidator;
import UTIL.TableManager;
import VIEW.ProductsUI;
import datechooser.beans.DateChooserCombo;
import java.awt.Point;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JDialog;
import javax.swing.JFileChooser;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JTable;
import javax.swing.JTextArea;
import javax.swing.JTextField;
import javax.swing.table.TableModel;


/**
 *
 * @author Belthazod
 */
public class TransferController {
    private final DatabaseConnector dbConnector; 
    private TableManager productTableManager;
    private TableManager transferTableManager;
    private TableManager transferHistoryTableManager;
    private JComboBox<ComboItem> transferFromComboBox;
    private JComboBox<ComboItem> transferToComboBox;
    private JComboBox<ComboItem> productFilterSupplierComboBox;
    private JComboBox<ComboItem> productFilterCategoryComboBox;
    ProductController productController;
    private JLabel clearMark;
    private JTextField productSearchInput; 
    private JDialog quantityInputDialog;
    private JTextField inputQuantityTextField;
    private JTextField editQuantity;
    private JTextArea remarksTextArea;
    private JDialog editQuantityDialog;
    private DateChooserCombo transferDate;
    private DateChooserCombo dateTo;
    private DateChooserCombo dateFrom;
    private JTable truckTable;
    /**
     * Creates an instance of Transfer Controller mapped to the Truck/Transfer Table
     * @param transferTable - the <code>JTable</code> containing the products available for transfered
     * to the other branches.
     * @param transferFromComboBox - the <code>JComboBox</code> containing the branches from which
     * the products are coming from
     * @param transferToComboBox - the <code>JComboBox</code> containing the branches to which the which
     * the products are to be transfered
     * 
     */
    public TransferController(JTextField tableSearchInput, JLabel clearMark, 
            JTable transferProductsTable, JTable transferTable, JComboBox<ComboItem> productFilterCategoryComboBox, 
            JComboBox<ComboItem> productFilterSupplierComboBox, JComboBox<ComboItem> transferFromComboBox, 
            JComboBox<ComboItem> transferToComboBox, JDialog quantityInputDialog, JTextField inputQuantityTextField,
            JTextField editQuantity, JDialog editQuantityDialog, DateChooserCombo transferDate, JTextArea remarksTextArea,
            DateChooserCombo dateTo, DateChooserCombo dateFrom, JTable historyTable) {
        
        productController = new ProductController();
        productTableManager = new TableManager(transferProductsTable);
        transferTableManager = new TableManager(transferTable);
        this.truckTable = transferTable;
        transferHistoryTableManager = new TableManager(historyTable);
        dbConnector= DatabaseConnector.getInstance();
        this.transferFromComboBox = transferFromComboBox;
        this.transferToComboBox = transferToComboBox;
        this.productFilterCategoryComboBox = productFilterCategoryComboBox;
        this.productFilterSupplierComboBox = productFilterSupplierComboBox;
        this.clearMark = clearMark;
        this.productSearchInput = tableSearchInput;
        this.quantityInputDialog = quantityInputDialog;
        this.inputQuantityTextField = inputQuantityTextField;           
        this.editQuantity = editQuantity;
        this.editQuantityDialog = editQuantityDialog;
        this.transferDate = transferDate;
        this.remarksTextArea = remarksTextArea;
        this.dateFrom = dateFrom;
        this.dateTo = dateTo;
        transferTableManager.hideColumn(0);
        transferTableManager.alignCellRight(3);
    }
    /**
     * Copies the selected product details from the Transfer Products Table
     * to the Truck Table.
     */
    public void addSelectedToTruck(){
        
        boolean itemExists = false;
        String productID = productTableManager.getIDFromTable(productTableManager.getSelectedRow());
        
        for(int row = 0; row < transferTableManager.getRowCount(); row++){
            String truckProductID = transferTableManager.getIDFromTable(row);
            itemExists = productID.equals(truckProductID);
        }
        if(itemExists){
            JOptionPane op = new JOptionPane("Item is already in the list.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Input Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
        }else{
            quantityInputDialog.setVisible(true);
        }
    }
    
    public void inputQuantityDialog(){
        try{
        String name = (String) productTableManager.getValueAt(productTableManager.getSelectedRow(), 1);
        String quantity = (String) productTableManager.getValueAt(productTableManager.getSelectedRow(), 3);
        String productID = productTableManager.getIDFromTable(productTableManager.getSelectedRow());
        String inputQuantity = inputQuantityTextField.getText();
        
        int currentQuantity = Integer.parseInt(quantity);
        int inputQty = Integer.parseInt(inputQuantity);
        if(inputQty > 0){
        if(currentQuantity >= inputQty){
            transferTableManager.addRowContent(new String[]{productID, name, quantity, inputQuantity});
            quantityInputDialog.dispose();
        }else{
            JOptionPane.showMessageDialog(null, "The quantity you have entered is greater than the current stock. Please check the current stock and try again.", "Input Error", JOptionPane.ERROR_MESSAGE);
        }
        }else{
            JOptionPane.showMessageDialog(null, "Please input a valid quantity.", "Quantity input Error", JOptionPane.ERROR_MESSAGE);
        }
        }catch(NumberFormatException nfe){
            JOptionPane.showMessageDialog(null, "Please input a valid quantity.", "Quantity input Error", JOptionPane.ERROR_MESSAGE);
        }
    }
    
    /**
     * Gets all the products enlisted in the "Truck" <code>JTable</code> and 
     * creates a transfer and transfer_details topple(s) based on the values 
     * in the table.
     */
    public void transferProducts(){
        try{
            String transferFrom = transferFromComboBox.getSelectedItem().toString();
            String transferTo = transferToComboBox.getSelectedItem().toString();
            String remarks = remarksTextArea.getText();
            long timeNow = System.currentTimeMillis();
            String today = InputValidator.dateFormat(transferDate);
            boolean mainIsSelected = false;
            int mainCount = 0;
            boolean invalidQuantityFlag = false;
            boolean greaterThanCurrent = false;
            for(int row = 0; row < transferTableManager.getRowCount();row++){
                int currentQuantity = Integer.parseInt(transferTableManager.getValueAt(
                        row, 3));
                int newQuantity = Integer.parseInt(transferTableManager.getValueAt(
                        row, 3));
                if(newQuantity <= 0){
                    invalidQuantityFlag = true;
                    break;
                }
                if(currentQuantity < newQuantity){
                    greaterThanCurrent = true;
                }
                
            }
            //checks if the "Main" branch is selected in either comboBox
            if(transferFrom.equals("Main Branch")){
                mainIsSelected = true;
                mainCount++;
            }
            if(transferTo.equals("Main Branch")){
                mainIsSelected = true;
                mainCount++;
            }
            if(!invalidQuantityFlag && !greaterThanCurrent){
                if(mainIsSelected && mainCount != 2){
                    int result = JOptionPane.showConfirmDialog(null, "Confirm Transfer?", "Confirm Transfer", JOptionPane.YES_NO_OPTION);
                    if(result == 0){
                        dbConnector.insert("INSERT INTO transfers (transfer_date, "
                                + "transfer_from, destination, remarks) VALUES(?,?,?,?)", 
                                new String[]{today.toString(),transferFrom, transferTo, remarks});
                        ResultSet rs = dbConnector.query("SELECT transfer_id FROM transfers ORDER BY 1 DESC");
                        rs.next();
                        String transferID = rs.getString(1);
                        int count =0;
                        for(int row = 0; row < transferTableManager.getRowCount(); row++){
                            Integer quantityToTransfer = Integer.parseInt(transferTableManager.getValueAt(row, 3));
                            //Integer currentQuantity = Integer.parseInt(transferTableManager.getValueAt(row, 3));    
                            String productID = transferTableManager.getIDFromTable(row);
                            if(quantityToTransfer > 0){
                                dbConnector.insert("INSERT INTO transferdetails "
                                        + "(transfer_id, product_id, qty_transfered) "
                                        + "VALUES(?,?,?)", new String[]{transferID, productID, quantityToTransfer.toString()});
                                count++;
                                if(transferFrom.equals("Main Branch")){
                                    dbConnector.update("UPDATE product SET physical_count = physical_count - ? WHERE product_id = ?", new String[]{quantityToTransfer.toString(), productID} );
                                }else{
                                    dbConnector.update("UPDATE product SET physical_count = physical_count + ? WHERE product_id = ?", new String[]{quantityToTransfer.toString(), productID} );
                                }
                            }
                        }
                        remarksTextArea.setText("");
                        ProductsUI.updateAdminProductsTable();                        
                        dbConnector.closeConnection();

                        JOptionPane.showMessageDialog(null, count+ " product(s) transferred successfully", "Transferred Successful", JOptionPane.INFORMATION_MESSAGE);
                        saveTransferReportFile();
                        transferTableManager.clearTableContents();
                                                
                    }
                }else{
                    JOptionPane.showMessageDialog(null, "Please check the sender and recipient of the transfer. The Main branch should be in either of the two not both.", "Branch location error.", JOptionPane.ERROR_MESSAGE);
                }
            }
            if(invalidQuantityFlag){
                JOptionPane op = new JOptionPane("Product quantity invalid. The quantity should not be empty, equal to 0, or less than 0.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Input Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
                //JOptionPane.showMessageDialog(null, "Invalid Quantity. The quantity should not be empty, equal to 0, or less that 0", "Input Error", JOptionPane.ERROR_MESSAGE);
            }
            if(greaterThanCurrent){
                JOptionPane op = new JOptionPane("Please check the quantity of each product. The quantity to be delivered cannot exceed the current product count in stock.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Input Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
                //JOptionPane.showMessageDialog(null, "Invalid Quantity. The quantity to be delivered cannot exceed the current product count in stock.", "Input Error", JOptionPane.ERROR_MESSAGE);
            }
            
            
            
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
            JOptionPane op = new JOptionPane("Failed to transfer products.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Database Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
            // JOptionPane.showMessageDialog(null, "Failed to transfer products", "Database error.", JOptionPane.ERROR_MESSAGE);
        }catch(IndexOutOfBoundsException ioobe){
            ioobe.printStackTrace();
            JOptionPane op = new JOptionPane("Please input the quantity of the item/s.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Input Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
            //JOptionPane.showMessageDialog(null, "Please input the quantity of the item/s.");
        }catch(NumberFormatException nfe){
            nfe.printStackTrace();
            JOptionPane op = new JOptionPane("Please enter a valid quantity.",JOptionPane.ERROR_MESSAGE);
                JDialog dialog = op.createDialog("Input Error");
                dialog.setAlwaysOnTop(true); //<-- this line
                dialog.setModal(true);
                dialog.setDefaultCloseOperation(JDialog.DISPOSE_ON_CLOSE);
                dialog.setVisible(true);
            //JOptionPane.showMessageDialog(null, "Please input a valid quantity.");
        }
    }
    
    /**
     * Removes the selected row in the Truck<code>JTable</code>
     */
    public void removeSelectedRow(){
        transferTableManager.deleteRow();
    }
    
    public void clearList(){
        int result = JOptionPane.showConfirmDialog(null, "Are you sure you want to clear the list?", "Confirm Checkout", JOptionPane.YES_NO_OPTION);
        if(result == 0){
        transferTableManager.clearTableContents();
    }
    }
    
     public void filterProducts(){

         ArrayList<Product> productList = productController.getProducts();
        
         if(productFilterCategoryComboBox.getSelectedItem()!=null && productList != null && productFilterSupplierComboBox.getSelectedItem()!=null){
            productTableManager.clearTableContents();
            ArrayList<Product> filteredByCategoryList = filterByCategory( productList);
            ArrayList<Product> filteredByCategoryAndSupplierList = filterBySupplier(filteredByCategoryList);
            
            if(clearMark.isVisible()){
                searchProduct();
            }else{
                for(Product product:filteredByCategoryAndSupplierList){

                        String[] productValues = {product.getProductID(), product.getName(), product.getTypeName(),
                        product.getPhysicalCount(), 
                        product.getReorderQuantityLevel()};

                        productTableManager.addRowContent(productValues);
                  
                }
            }
         }        
    }
    
         public ArrayList<Product> filterByCategory(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(productFilterCategoryComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(productFilterCategoryComboBox.getSelectedItem().toString().equals(product.getTypeName())){
                    filteredProductList.add(product);
                }
            }
            return filteredProductList;
         }
     }
         
         public ArrayList<Product> filterBySupplier(ArrayList<Product> productList){
         ArrayList<Product> filteredProductList = new ArrayList<Product>();
         if(productFilterSupplierComboBox.getSelectedItem().toString().equals("All")){
             return productList;
         }else{
            for(Product product:productList){
                if(productFilterSupplierComboBox.getSelectedItem().toString().equals(product.getSupplierName())){
                    filteredProductList.add(product);
                }
            }
            return filteredProductList;
         }
     }
    
        public void searchProduct(){
        String productName = productSearchInput.getText();
        
        ArrayList<Product> productList = productController.getProducts();
        productTableManager.clearTableContents();
        
        ArrayList<Product> filteredByCategoryList = filterByCategory(productList);
        ArrayList<Product> filteredBySupplierAndCategoryList = filterBySupplier(filteredByCategoryList);
        for(Product product: filteredBySupplierAndCategoryList){
            if(product.getName().toLowerCase().contains(productName.toLowerCase())){
                String[] adminCompleteValues = {product.getProductID(), product.getName(), product.getTypeName(),                     
                    product.getPhysicalCount(), 
                    product.getReorderQuantityLevel()};
                productTableManager.addRowContent(adminCompleteValues);
            }
        }
        if(productName.equals("")){
            clearMark.setVisible(false);
        }else{
            clearMark.setVisible(true);
        }
    }
    
    public void clearSearch(){
        productSearchInput.setText("");
        searchProduct();
        clearMark.setVisible(false);
    }
    public void editDialog(){
        editQuantityDialog.setVisible(true);
    }
    
    public void editQuantityDialog(){
         String quantity = editQuantity.getText();
         String physCount = transferTableManager.getValueAt(transferTableManager.getSelectedRow(), 2);
         int quant = Integer.parseInt(quantity);
         int count = Integer.parseInt(physCount);
         if(count >= quant){
             if(quant > 0){
                transferTableManager.setValueAt(transferTableManager.getSelectedRow(), 3, quantity);
                editQuantity.setText("");
                editQuantityDialog.dispose();
             }else{
                JOptionPane.showMessageDialog(null, "Invalid Input.", "Input Error", JOptionPane.ERROR_MESSAGE);
             }
         }else{
             editQuantity.setText("");
             JOptionPane.showMessageDialog(null, "Insufficient stock.", "Input Error", JOptionPane.ERROR_MESSAGE);
         }
     }
    
    public void justDClickIt(JTable table){
    table.addMouseListener(new MouseAdapter() {
    public void mousePressed(MouseEvent me) {
        JTable table =(JTable) me.getSource();
        Point p = me.getPoint();
        int row = table.rowAtPoint(p);
        if (me.getClickCount() == 2) {
        addSelectedToTruck();
        }
    }
});
    }
         
         public void justDClickIt2(JTable table){
    table.addMouseListener(new MouseAdapter() {
    public void mousePressed(MouseEvent me) {
        JTable table =(JTable) me.getSource();
        Point p = me.getPoint();
        int row = table.rowAtPoint(p);
        if (me.getClickCount() == 2) {
            editDialog();
        }
    }
});
    }
         
    public void updateTransferHistoryTable(){
        DateFormat dfto = new SimpleDateFormat("yyyy-MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("MMMMM d, yyyy");
        String toDate = InputValidator.dateFormat(dateFrom);
        String fromDate = InputValidator.dateFormat(dateTo);
        try{
            ResultSet rs = dbConnector.query("SELECT Transfer_date, Name, qty_transfered, Transfer_from, Destination FROM transferdetails JOIN product using(Product_id) JOIN transfers using(Transfer_id) WHERE Transfer_date between '" + toDate + "' and '" + fromDate + "' ORDER BY 1 DESC");
            transferHistoryTableManager.clearTableContents();
            while(rs.next()){
                String date = rs.getString("Transfer_date");
                java.util.Date dateFormat = dfto.parse(date);
                String dateReport = sdf.format(dateFormat);
                String name = rs.getString("Name");
                String quantity = rs.getString("qty_transfered");
                String description = "from " + rs.getString("Transfer_from") + " to " + rs.getString("Destination");
                //String remarks = rs.getString("remarks");
                String value1 = rs.getString("Transfer_from");
                String value2 = rs.getString("Destination");
                
                String[] reportDetails = {dateReport, name, quantity, value1, value2, "N/A"};
                transferHistoryTableManager.addRowContent(reportDetails);
            }
        }catch(SQLException sqlE){
            sqlE.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }
                
    }
    
    public void transferToExcel(JTable table, File file){
		try{
			TableModel model = table.getModel();
			FileWriter excel = new FileWriter(file);
                        String date = transferDate.getText();
                        String remarks = remarksTextArea.getText();
                        String from = transferFromComboBox.getSelectedItem().toString();
                        String to = transferToComboBox.getSelectedItem().toString();
                        int columns = model.getColumnCount();
                        int col = columns - 1;
			for(int i = 1; i < col; i++){
				excel.write(model.getColumnName(i) + "\t");
			}
                        excel.write("Quantity Transfered\t");
                        excel.write("Date\t");
                        excel.write("Transfer From\t");
                        excel.write("Transfer To\t");
                        excel.write("Remarks\t");
			excel.write("\n");
                                                
			for(int i=0; i< model.getRowCount(); i++) {
				for(int j=1; j < model.getColumnCount(); j++) {                                                                                                                
                                        excel.write(model.getValueAt(i,j).toString()+"\t");                                        
                                }
                                    try{
                                        excel.write(date+"\t");
                                        excel.write(from+"\t");
                                        excel.write(to+"\t");
                                        excel.write(remarks+"\t");
                                    }catch(NullPointerException npe){
                                        
                                        excel.write(date+"\t");
                                        excel.write(from+"\t");
                                        excel.write(to+"\t");
                                        excel.write("\t");
                                    }
                                
				excel.write("\n");
			}
                        
			excel.close();
		}catch(IOException e){ 
                    e.printStackTrace();
                }
	}
        
        public void saveTransferReportFile(){        
            JFileChooser fc = new JFileChooser();
                int option = fc.showSaveDialog(null);
                if(option == JFileChooser.APPROVE_OPTION){
                    String filename = fc.getSelectedFile().getName(); 
                    String path = fc.getSelectedFile().getParentFile().getPath();

					int len = filename.length();
					String ext = "";
					String file = "";

					if(len > 4){
						ext = filename.substring(len-4, len);
					}

					if(ext.equals(".xls")){
						file = path + "\\" + filename; 
					}else{
						file = path + "\\" + filename + ".xls"; 
					}
					transferToExcel(truckTable, new File(file));
				}
        }
}
